<?php
require '../utils/conn.php';
include '../models/MainWord.php';
include '../models/Meaning.php';
include '../models/Synonym.php';
include '../models/Antonym.php';
include '../models/Sentence.php';

function addMainWord($mainWord){
	$sql = "SELECT * FROM main_word WHERE content='$mainWord->content' and category='$mainWord->category'";
	$result = mysql_query($sql);
	
	if ($myrow = mysql_fetch_array($result)) {
		echo "error:该单词已存在！";
		return;
	}

	$content = $mainWord->content;
	if ($mainWord->wordIndex != 1) {
		$wordIndex = strtoupper(substr($content, 0,1));
		$mainWord->wordIndex = $wordIndex;
	}
	else {
		$mainWord->wordIndex = "update";
	}

	$currentTime = time();
	$mainWord->createTime = $currentTime;
	$query = "INSERT INTO main_word(content, word_index, slug, create_time, category)" . " VALUES('$content','$mainWord->wordIndex','$mainWord->slug','$currentTime','$mainWord->category')";
	
	$result = mysql_query($query) or die("插入单词出现错误：". mysql_error());
	$mainWord->wordId = mysql_insert_id();
	return $mainWord;
}

function getMainWordByContent($content, $category) {
	$query = "SELECT * FROM main_word WHERE content='$content' and category='$category'";
	$result = mysql_query($query)  or die("查询单词出现错误：". mysql_error());

	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	
	if (!$row) {
		echo "No Reuslt";
		return null;
	}
	
	$mainWord = new MainWord();
	$mainWord->setWordId($row['word_id']);
	$mainWord->setContent($row['content']);
	$mainWord->setSlug($row['slug']);
	$mainWord->setCreateTime($row['create_time']);
	$mainWord->setWordIndex($row['word_index']);
	$mainWord->setCategory($row['category']);
	
	$wordId = $mainWord->wordId;
	$query = "SELECT * FROM word_meaning WHERE word_id=".$wordId;
	$result = mysql_query($query);
	$meaningArray = array();
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$meaningId = $row['meaning_id'];
		$meaning = getMeaningById($meaningId);
		$meaningArray[] = $meaning;
	}
	$mainWord->meanings = $meaningArray;

	return $mainWord;
}

function getMainWordById($wordId) {
	$query = "SELECT * FROM main_word WHERE word_id = ".$wordId;
	$result = mysql_query($query)  or die("查询单词出现错误：". mysql_error());;
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$mainWord = new MainWord();
	$mainWord->setWordId($row['word_id']);
	$mainWord->setContent($row['content']);
	$mainWord->setSlug($row['slug']);
	$mainWord->setCreateTime($row['create_time']);
	$mainWord->setWordIndex($row['word_index']);
	$mainWord->setCategory($row['category']);

	$wordId = $mainWord->wordId;
	$query = "SELECT * FROM word_meaning WHERE word_id=".$wordId;
	$result = mysql_query($query);
	$meaningArray = array();
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$meaningId = $row['meaning_id'];
		$meaning = getMeaningById($meaningId);
		$meaningArray[] = $meaning;
	}
	$mainWord->meanings = $meaningArray;

	return $mainWord;
}


function getAllWords()
{
	$words = array();
	$query = "SELECT word_id FROM main_word";
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$wordId = $row['word_id'];
		$word = getMainWordById($wordId);
		$words[] = $word;
	}
	return $words;
}

function getWordsByLetter($letter, $category)
{
	$words = array();
	$query = "SELECT word_id FROM main_word order by content ASC";
	
	if ($category != "") {
		$query = "SELECT word_id FROM main_word WHERE category='$category' order by content ASC";;
	}
	
	if ($letter != "") {
		$query = "SELECT word_id FROM main_word WHERE word_index='$letter' and category='$category' order by content ASC";
	}
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$wordId = $row['word_id'];
		$word = getMainWordById($wordId);
		$words[] = $word;
	}
	return $words;
}

function updateMainWord($mainWord) {
	
	$content = $mainWord->content;
	if ($mainWord->wordIndex != 1) {
		$wordIndex = strtoupper(substr($content, 0,1));
		$mainWord->wordIndex = $wordIndex;
	}
	else {
		$mainWord->wordIndex = "update";
	}
	$currentTime = time();
	$query = "UPDATE main_word SET content='$content', word_index='$mainWord->wordIndex', slug='$mainWord->slug', create_time='$currentTime', category='$mainWord->category' WHERE word_id=".$mainWord->wordId;
	mysql_query($query) or die("更新单词出现错误：". mysql_error());
}

function deleteMainWordById($wordId){
	$query0 = "SELECT word_index FROM main_word WHERE word_id=".$wordId;
	$result0 = mysql_query($query0);
	$letter;
	while ($row = mysql_fetch_array($result0, MYSQL_ASSOC))
	{
		$letter = $row['word_index'];
	} 
	$query = "DELETE FROM main_word WHERE word_id=".$wordId;
	echo $query;
	mysql_query($query) or die("删除单词出现错误：". mysql_error());
	
	$query = "SELECT meaning_id FROM word_meaning WHERE word_id=".$wordId;
	echo $query;
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$meaningId = $row['meaning_id'];
		deleteMeaningById($meaningId);
	} 
	
	$query = "DELETE FROM word_meaning WHERE word_id=".$wordId;
	echo $query;
	mysql_query($query);
	
	$query1 = "SELECT word_id FROM main_word WHERE word_index='$letter' limit 1";
	echo "GET Letter:	".$query1;
	$result1 = mysql_query($query1);
	while ($row1 = mysql_fetch_array($result1, MYSQL_ASSOC))
	{
		$wordId = $row1['word_id'];
		$currentTime = time();
		$query = "UPDATE main_word SET create_time='$currentTime' WHERE word_id=".$wordId;
		echo $query;
		mysql_query($query) or die("更新单词出现错误：". mysql_error());
	} 
	
}


function addMeaning($meaning, $wordId) {
	$query = "INSERT INTO meaning(cn_translate,eng_translate)"." VALUES('$meaning->cnTranslate', '$meaning->engTranslate')";
	$result = mysql_query($query) or die("插入含义出现错误：". mysql_error());
	$meaning->meaningId = mysql_insert_id();

	$query = "INSERT INTO word_meaning(word_id, meaning_id)"." VALUES('$wordId','$meaning->meaningId')";
	mysql_query($query) or die("插入含义出现错误：". mysql_error());

	return $meaning;
}

function getMeaningById($meaningId) {
	$query = "SELECT * FROM meaning WHERE meaning_id=".$meaningId;
	$result = mysql_query($query)  or die("查询含义出现错误：". mysql_error());
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$meaning = new Meaning();
	$meaning->setMeaningId($row['meaning_id']);
	$meaning->setCnTranslate($row['cn_translate']);
	$meaning->setEngTranslate($row['eng_translate']);

	$query = "SELECT * FROM meaning_synonym WHERE meaning_id=".$meaningId;
	$result = mysql_query($query);
	$synonymArray = array();
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$synonymId = $row['synonym_id'];
		$synonym = getSynonymById($synonymId);
		$synonymArray[] = $synonym;
	}
	$meaning->synonyms = $synonymArray;

	$query = "SELECT * FROM meaning_antonym WHERE meaning_id=".$meaningId;
	$result = mysql_query($query);
	$antonymArray = array();
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$antonymId = $row['antonym_id'];
		$antonym = getAntonymById($antonymId);
		$antonymArray[] = $antonym;
	}
	$meaning->antonyms = $antonymArray;

	$query = "SELECT * FROM meaning_sentence WHERE meaning_id=".$meaningId;
	$result = mysql_query($query);
	$sentenceArray = array();
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$sentenceId = $row['sentence_id'];
		$sentence = getSentenceById($sentenceId);
		$sentenceArray[] = $sentence;
	}
	$meaning->sentences = $sentenceArray;

	return $meaning;
}

function updateMeaning($meaning) {
	$query = "UPDATE meaning SET cn_translate='$meaning->cnTranslate', eng_translate='$meaning->engTranslate' WHERE meaning_id=".$meaning->meaningId;
	mysql_query($query) or die("更新含义出现错误：". mysql_error());
}

function deleteMeaningById($meaningId) {
	$query = "DELETE FROM meaning WHERE meaning_id=".$meaningId;
	echo $query;
	mysql_query($query) or die("删除含义出现错误：". mysql_error());
	
	$query = "SELECT synonym_id FROM meaning_synonym WHERE meaning_id=".$meaningId;
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$synonymId = $row['synonym_id'];
		deleteSynonymById($synonymId);
	} 
	
	$query = "DELETE FROM meaning_synonym WHERE meaning_id=".$meaningId;
	echo $query;
	mysql_query($query);
	
	$query = "SELECT antonym_id FROM meaning_antonym WHERE meaning_id=".$meaningId;
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$antonymId = $row['antonym_id'];
		deleteAntonymById($antonymId);
	}
	 
	$query = "DELETE FROM meaning_antonym WHERE meaning_id=".$meaningId;
	echo $query;
	mysql_query($query);
	
	$query = "SELECT sentence_id FROM meaning_sentence WHERE meaning_id=".$meaningId;
	echo $query;
	$result = mysql_query($query);
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$sentenceId = $row['sentence_id'];
		deleteSentenceById($sentenceId);
	} 
	
	$query = "DELETE FROM meaning_sentence WHERE meaning_id=".$meaningId;
	echo $query;
	mysql_query($query);
	
	$query = "DELETE FROM word_meaning WHERE meaning_id=".$meaningId;
	echo $query;
	mysql_query($query);
}

function addSynonym($synonym, $meaningId) {
	$query = "INSERT INTO synonym(content)"." VALUES('$synonym->content')";
	$result = mysql_query($query) or die("插入同义词出现错误：". mysql_error());
	$synonym->synonymId = mysql_insert_id();

	$query = "INSERT INTO meaning_synonym(meaning_id, synonym_id)"." VALUES('$meaningId','$synonym->synonymId')";
	mysql_query($query) or die("插入同义词出现错误：". mysql_error());

	return $synonym;
}

function getSynonymById($synonymId)
{
	$query = "SELECT * FROM synonym WHERE synonym_id=".$synonymId;
	$result = mysql_query($query)  or die("查询同义词出现错误：". mysql_error());
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$synonym = new Synonym();
	$synonym->setSynonymId($row['synonym_id']);
	$synonym->setContent($row['content']);
	return $synonym;
}

function deleteSynonymById($synonymId) {
	$query = "DELETE FROM synonym WHERE synonym_id=".$synonymId;
	echo $query;
	mysql_query($query) or die("删除同义词出现错误：". mysql_error());
	
	$query = "DELETE FROM meaning_synonym WHERE synonym_id=".$synonymId;
	echo $query;
	mysql_query($query) or die("删除同义词出现错误：". mysql_error());
}


function addAntonym($antonym, $meaningId) {
	$query = "INSERT INTO antonym(content)"." VALUES('$antonym->content')";
	$result = mysql_query($query) or die("插入反义词出现错误：". mysql_error());
	$antonym->antonymId = mysql_insert_id();

	$query = "INSERT INTO meaning_antonym(meaning_id, antonym_id)"." VALUES('$meaningId','$antonym->antonymId')";
	mysql_query($query) or die("插入反义词出现错误：". mysql_error());

	return $antonym;
}

function getAntonymById($antonymId)
{
	$query = "SELECT * FROM antonym WHERE antonym_id=".$antonymId;
	$result = mysql_query($query)  or die("查询反义词出现错误：". mysql_error());
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$antonym = new Antonym();
	$antonym->setAntonymId($row['antonym_id']);
	$antonym->setContent($row['content']);
	return $antonym;
}

function deleteAntonymById($antonymId) {
	$query = "DELETE FROM antonym WHERE antonym_id=".$antonymId;
	echo $query;
	mysql_query($query) or die("删反同义词出现错误：". mysql_error());
		
	$query = "DELETE FROM meaning_antonym WHERE antonym_id=".$antonymId;
	echo $query;
	mysql_query($query) or die("删除同义词出现错误：". mysql_error());
}

function addSentence($sentence, $meaningId) {
	$query = "INSERT INTO sentence(content)"." VALUES('$sentence->content')";
	$result = mysql_query($query) or die("插入例句出现错误：". mysql_error());
	$sentence->sentenceId = mysql_insert_id();

	$query = "INSERT INTO meaning_sentence(meaning_id, sentence_id)"." VALUES('$meaningId','$sentence->sentenceId')";
	mysql_query($query) or die("插入例句出现错误：". mysql_error());

	return $sentence;
}

function getSentenceById($sentenceId)
{
	$query = "SELECT * FROM sentence WHERE sentence_id=".$sentenceId;
	$result = mysql_query($query)  or die("查询例句出现错误：". mysql_error());
	$row = mysql_fetch_array($result, MYSQL_ASSOC);
	$sentence = new Sentence();
	$sentence->setSentenceId($row['sentence_id']);
	$sentence->setContent($row['content']);
	return $sentence;
}

function deleteSentenceById($sentenceId) {
	$query = "DELETE FROM sentence WHERE sentence_id=".$sentenceId;
	echo $query;
	mysql_query($query) or die("删除例句出现错误：". mysql_error());
	
	$query = "DELETE FROM meaning_sentence WHERE sentence_id=".$sentenceId;
	echo $query;
	mysql_query($query) or die("删除同义词出现错误：". mysql_error());
}

function updateWordEditTime($wordId){
	$currentTime = time();
	$query = "UPDATE main_word SET create_time='$currentTime' WHERE word_id=".$wordId;
	mysql_query($query) or die("更新单词出现错误：". mysql_error());
}

function getUpdateLetters($timestamp, $category){
	$query = "SELECT word_index FROM main_word WHERE create_time>".$timestamp." and category=".$category." order by word_index ASC";
	$result = mysql_query($query);
	$wordIndexes = array();
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$wordIndexes[] = $row['word_index'];
	}
	$wordIndexes = array_flip(array_flip($wordIndexes));
	$letters = array();
	foreach ($wordIndexes as $letter)
	{
		$letters[] = $letter;
	}
	return $letters;
}

?>